from flask import Flask, render_template, request, make_response, redirect
import pymysql
from time import sleep
import re
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship, Session
from sqlalchemy import create_engine, Table, MetaData

app = Flask(__name__)
host = "127.0.0.1"

coursepeople = {}
canselect = 1

def isIntSeriously(number):
    result = False
    try:
        n = float(number)
        if n.is_integer() and str(number).count('.') == 0:
            result = True
    except:
        return False

    return result


def man_login():
    id = request.cookies.get('user')
    pas = request.cookies.get('pass')
    if not isIntSeriously(id):
        return False
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sql = "SELECT Mno,Mpassword from manager where Mno = {}".format(id)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(len(result))
    print(result)
    if len(result) > 0:
        if result[0][1] == pas:
            return True
        else:
            return False
    else:
        return False


def user_login():
    id = request.cookies.get('user')
    pas = request.cookies.get('pass')
    if not isIntSeriously(id):
        return False

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sql = "SELECT Sno,Spassword from student where Sno = {}".format(id)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(len(result))
    print(result)
    if len(result) > 0:
        if result[0][1] == pas:
            return True
        else:
            return False
    else:
        return False


@app.route('/testpost', methods=['GET', 'POST'])
def testpost():
    return render_template("xuankelist.html")


@app.route('/', methods=['GET', 'POST'])
def hello_world():
    return render_template("login/login.html")

@app.route('/initial')
def initial():
    return render_template("initial.html")

# 2021 11-3 22:39 cjc
@app.route("/modifypass/<new>", methods=['GET', 'POST'])
def delet(new):
    print(new)
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    id = request.cookies.get('user')
    sql = "UPDATE manager SET Mpassword = {} WHERE Mno = {}".format(new, id)  # 这里需要cookie的管理员账号
    print(sql)
    cur.execute(sql)
    conn.commit()
    print("接收到")
    return "200"


@app.route("/modifypassword", methods=['GET', 'POST'])
def modifypassword():
    print(type(request.method))
    if request.method == 'GET':
        return render_template("modify_password(1).html")
@app.route("/stumodifypassword", methods=['GET', 'POST'])
def stumodifypassword():
    print(type(request.method))
    if request.method == 'GET':
        return render_template("stumodify_password.html")


@app.route("/modify", methods=['POST'])
def modify():
    newpass = request.form['newpass']
    confirmpass = request.form['confirmpass']
    print(newpass)
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    id = request.cookies.get('user')
    sql = "UPDATE manager SET Mpassword = '{}' WHERE Mno = {}".format(newpass, id)  # 这里需要cookie的管理员账号
    print(sql)
    cur.execute(sql)
    conn.commit()
    resp = make_response(render_template("index.html"))
    resp.set_cookie("pass", newpass, max_age=3600)
    if newpass == confirmpass:
        return resp

@app.route("/stumodify", methods=['POST'])
def stumodify():
    newpass = request.form['newpass']
    confirmpass = request.form['confirmpass']
    print(newpass)

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    id = request.cookies.get('user')
    sql = "select Sname from student where Sno='{}'".format(id)

    print(sql)
    cur.execute(sql)
    sname = cur.fetchall()[0][0]
    print(sname)


    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    id = request.cookies.get('user')
    sql = "UPDATE student SET Spassword = '{}' WHERE Sno = {}".format(newpass, id)  # 这里需要cookie的学生账号
    print(sql)
    cur.execute(sql)
    conn.commit()
    resp = make_response(render_template("stuindex.html",sname=sname))
    resp.set_cookie("pass", newpass, max_age=3600)
    if newpass == confirmpass:
        return resp

"""
登录
"""


@app.route('/login', methods=['POST'])
def log():
    who = request.form['user']
    id = request.form['name']
    pas = request.form['pass']
    # resp = make_response(render_template("login/index.html"))
    resp = make_response(redirect("http://127.0.0.1:8080/loading/{}".format(who)))
    resp.set_cookie("user", id, max_age=3600)
    resp.set_cookie("pass", pas, max_age=3600)
    # user_login()
    return resp


@app.route('/loading/<user>')
def load(user):
    if user == "common":
        can = user_login()
    else:
        can = man_login()

    if can and user != "common":
        # return str(request.cookies.get("user")) + " " + str(request.cookies.get("pass")+" "+user)
        return render_template("index.html")
    elif can and user == "common":
        conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                               charset="utf8")
        cur = conn.cursor()
        id = request.cookies.get('user')
        sql = "select Sname from student where Sno='{}'".format(id)

        print(sql)
        cur.execute(sql)
        sname = cur.fetchall()[0][0]
        print(sname)
        return render_template("stuindex.html", sname=sname)
    else:

        return render_template("login/login2.html")


@app.route('/user/<username>')
def user(username):
    print(username)
    print(type(username))
    return 'hello ' + username


@app.route('/user/<username>/friends')
def user_friends(username):
    print(username)
    print(type(username))
    return 'hello ' + username


"""
超哥
11-02
"""


@app.route("/xuanlist")
def xuanlist():
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID"
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(result)
    return render_template("list.html", stu_info=result)


@app.route("/listse", methods=['POST'])
def listse():
    # HOSTNAME = '127.0.0.1'
    # PORT = '3306'
    # DATABASE = 'mysql'
    # USERNAME = 'root'
    # PASSWORD = '123456'
    # db_url = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(
    #     USERNAME,
    #     PASSWORD,
    #     HOSTNAME,
    #     PORT,
    #     DATABASE,
    # )
    # engin = create_engine(db_url)
    # session = Session(engin)
    # metadata = MetaData()
    # ex_table = Table('dbsc', metadata, autoload=True, autoload_with=engin)
    # res = session.execute(ex_table).all()
    # print("asd")
    # print(res)

    nam = request.form['name']
    if len(nam) == 0:
        nam = "%"
    id = request.form['id']
    if len(id) == 0:
        id = "%"
    course = request.form['cl']
    if len(course) == 0:
        course = "%"

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID where   student.Sname like '{}' and cast(Stu_Sno as char) like '{}' and course.Cname like '{}'".format(
        nam, id, course)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(result)
    return render_template("list2.html", stu_info=result)


# @app.route("/dele",methods=['POST'])
# def dele():
#     id = request.form['id']
#     cour  = request.form['cour']
#     # 返回的两个主键
#     conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
#                            charset="utf8")
#     cur = conn.cursor()
#     # 这里要修改成表合并再查询，然后传参数到list2,list2的数据还没改
#     sql = "SELECT ScID  FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID where  cast(Stu_Sno as char) like '{}' and course.Cname like '{}'".format(
#         id, cour)


@app.route("/dele/<id>/<cou>", methods=['GET', 'POST'])
def dele(id, cou):
    # a = request.args['callback']
    # print(a)
    print(id)
    print(cou)
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sql = "select CID from course where Cname='{}'".format(cou)

    print(sql)
    cur.execute(sql)
    couid = cur.fetchall()[0][0]
    print(couid)
    sql = "delete  from dbsc where Cou_CID={} and Stu_Sno={}".format(couid, id)
    print(sql)
    cur.execute(sql)
    conn.commit()

    coursepeople[cou] -= 1  # 减少人数放在最后

    print("接收到")
    return "200"


# 2021-11-3 14：30 cjc
# def transform_dic(two_list):
#     dic = {}
#     one_list = [x for item in two_list for x in item]
#     for i in one_list:


# 管理员界面的所有课程信息list
@app.route("/courselist")
def courselist():
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    # sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID"
    sql = "SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime,Cgrade,Cdept FROM course"
    sql1 = "SELECT Cname FROM course"
    dict = {}
    print("课程展示")
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    result = list(result)
    num = 0
    for i in result:
        result[num] = list(result[num])
        num += 1
    print(type(result))
    print(type(result[0]))
    print(result)

    num = 0
    for i in result:
        result[num].append(coursepeople[i[1]])
        num += 1
    print(result)
    print("课程展示结束")
    return render_template("courselist.html", cour_info=result)


# 管理员界面课程信息查询
@app.route("/courselistse", methods=['POST'])
def courselistse():
    CID = request.form['cid']
    if len(CID) == 0:
        CID = "%"
    Cname = request.form['cname']
    if len(Cname) == 0:
        Cname = "%"
    Cclasstime = request.form['classtime']
    if len(Cclasstime) == 0:
        Cclasstime = "%"

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    # sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID where   student.Sname like '{}' and cast(Stu_Sno as char) like '{}' and course.Cname like '{}'".format(nam,id,course)
    sql = "SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime,Cgrade,Cdept FROM course where cast(CID as char) like '{}' and Cname like '{}' and Cclasstime like '{}'".format(
        CID, Cname, Cclasstime)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(result)
    result = list(result)
    num = 0
    for i in result:
        result[num] = list(result[num])
        num += 1
    num = 0
    for i in result:
        result[num].append(coursepeople[i[1]])
        num += 1
    print(result)
    return render_template("courselistse.html", cour_info=result)


@app.route("/delecour/<cid>", methods=['GET', 'POST'])
def delecour(cid):
    # a = request.args['callback']
    # print(a)
    print(cid)

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    # sql = "select CID from course where Cname='{}'".format(cou)
    # print(sql)
    # cur.execute(sql)
    # couid = cur.fetchall()[0][0]
    # print(couid)
    sql = "delete from course where CID={}".format(cid)
    print(sql)
    cur.execute(sql)
    conn.commit()
    print("接收到")
    return "200"


# 管理员界面的所有学生信息list
@app.route("/studentlist")
def studentlist():
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    # sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID"
    # sql="SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime,Cgrade,Cdept FROM course"
    sql = "SELECT Sno,Spassword,Sname,Ssex,Sage,Sdept,Sgrade FROM student"
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(result)
    return render_template("studentlist.html", stu_info=result)


# 管理员界面学生信息查询
@app.route("/studentlistse", methods=['POST'])
def studentlistse():
    Sno = request.form['sno']
    if len(Sno) == 0:
        Sno = "%"
    Sdept = request.form['sdept']
    if len(Sdept) == 0:
        Sdept = "%"
    Sgrade = request.form['sgrade']
    if len(Sgrade) == 0:
        Sgrade = "%"

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    # sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID where   student.Sname like '{}' and cast(Stu_Sno as char) like '{}' and course.Cname like '{}'".format(nam,id,course)
    sql = "SELECT Sno,Spassword,Sname,Ssex,Sage,Sdept,Sgrade FROM student where cast(Sno as char) like '{}' and Sdept like '{}' and Sgrade like '{}'".format(
        Sno, Sdept, Sgrade)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(result)
    return render_template("studentlistse.html", stu_info=result)


@app.route("/delestu/<sno>", methods=['GET', 'POST'])
def delestu(sno):
    # a = request.args['callback']
    # print(a)
    print(sno)

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    # sql = "select CID from course where Cname='{}'".format(cou)
    # print(sql)
    # cur.execute(sql)
    # couid = cur.fetchall()[0][0]
    # print(couid)
    sql = "delete from student where Sno={}".format(sno)
    print(sql)
    cur.execute(sql)
    conn.commit()
    print("接收到")
    return "200"


'''
增加课程
'''


@app.route("/putcourse", methods=['POST'])
def putcourse():
    idid = request.form['idid']
    name = request.form['name']
    people = request.form['people']
    score = request.form['score']
    starttime = request.form['starttime']
    endtime = request.form['endtime']
    place = request.form['place']
    tea = request.form['tea']
    ttc = request.form['ttc']
    grade = request.form['grade']
    xi = request.form['xi']
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    try:
        sql = "INSERT into course VALUES({},'{}',{},{},'{}','{}','{}','{}','{}','{}','{}')".format(idid, name, people,
                                                                                                   score, starttime,
                                                                                                   endtime, place, tea,
                                                                                                   ttc, grade, xi)
        print(sql)
        cur.execute(sql)
        conn.commit()
        coursepeople[name] = 0
        print(coursepeople)
        # sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID"
        sql = "SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime,Cgrade,Cdept FROM course"
        sql1 = "SELECT Cname FROM course"
        dict = {}
        print("课程展示")
        print(sql)
        cur.execute(sql)
        result = cur.fetchall()
        result = list(result)
        num = 0
        for i in result:
            result[num] = list(result[num])
            num += 1
        # print(type(result))
        # print(type(result[0]))
        print(result)
        num = 0
        for i in result:
            result[num].append(coursepeople[i[1]])
            num += 1
        print(result)
        print("课程展示结束")
        return render_template("courselist.html", cour_info=result)
    except:
        return "信息都不打完，你加个锤子"


@app.route("/putstudent", methods=['POST'])
def putstudent():
    idid = request.form['idid']
    ps = request.form['ps']
    name = request.form['name']
    age = request.form['age']
    xi = request.form['xi']
    grade = request.form['grade']

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    try:
        sql = "INSERT into student VALUES({},'{}','{}','男',{},'{}','{}')".format(idid, ps, name, age, xi, grade)
        print(sql)
        cur.execute(sql)
        conn.commit()

        conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                               charset="utf8")
        cur = conn.cursor()
        # sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID"
        # sql="SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime,Cgrade,Cdept FROM course"
        sql = "SELECT Sno,Spassword,Sname,Ssex,Sage,Sdept,Sgrade FROM student"
        print(sql)
        cur.execute(sql)
        result = cur.fetchall()
        print(result)
        return render_template("studentlist.html", stu_info=result)
    except:
        return "信息都不打完，你加个锤子"


@app.route("/test2")
def test2():
    return render_template("2.html")


@app.route("/test3")
def test3():
    return render_template("3.html")


@app.route("/add")
def add():
    return render_template("add.html")


@app.route("/courseadd")
def courseadd():
    return render_template("courseadd.html")


@app.route("/studentadd")
def studentadd():
    return render_template("studentadd.html")


@app.route("/studentgai")
def studentgai():
    return render_template("studentgai.html")


# 注册
@app.route("/register")
def register():
    # resp = make_response(render_template("login/login.html"))
    return render_template("zhuce.html")


# 用户
@app.route("/registerload", methods=['POST'])
def registerload():
    if request.method == 'GET':
        print("1")
        return render_template("login/login.html")
    else:
        userName = request.form['userName']
        print(request.method)
        id = request.form['id']
        age = request.form['age']
        sex = request.form['sex']
        xi = request.form['xi']
        grade = request.form['grade']
        passw = request.form['pass']
        print(userName, id, age, sex, xi, grade, passw)
        print("这是post")
        if passw:
            conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                                   charset="utf8")
            cur = conn.cursor()
            try:
                sql = "INSERT into student VALUES({},'{}','{}','{}',{},'{}','{}')".format(id, passw, userName, sex, age,
                                                                                          xi, grade)
                cur.execute(sql)
                conn.commit()
            except:
                return "注册失败，重复的学号"
        return render_template("login/login.html")


# 管理员
@app.route("/registerloadguan", methods=['POST'])
def registerloadguan():
    if request.method == 'GET':
        print("1")
        return render_template("login/login.html")
    else:
        userName = request.form['userName']
        print(request.method)
        id = request.form['id']
        passw = request.form['pas']
        print(userName, id, passw)
        print("这是post")
        if passw:
            conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                                   charset="utf8")
            cur = conn.cursor()
            try:
                sql = "INSERT into manager VALUES({},'{}','{}')".format(id, passw, userName)
                cur.execute(sql)
                conn.commit()
            except:
                return "注册失败，重复的账号"
        return render_template("login/login.html")


# 退出登录
@app.route("/exitlogin")
def exitlogin():
    resp = make_response(render_template("login/login.html"))
    resp.delete_cookie("user")
    resp.delete_cookie("pass")
    return resp

#学生个人信息展示
@app.route("/per_info",methods=['GET','POST'])
def per_info():
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    id = request.cookies.get('user')
    sql = "SELECT Sno,Sname,Ssex,Sage,Sdept,Sgrade FROM student WHERE Sno = '{}' ".format(id)
    cur.execute(sql)
    result = cur.fetchall()
    cur.close()
    conn.close()
    return render_template("stu_information.html",stu_info=result)

# 2021 11-4 19：30 cjc
# 学生端选课界面
@app.route("/xuankelist")
def xuankelist():
    if canselect==1:
        conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                               charset="utf8")
        cur = conn.cursor()
        id = request.cookies.get('user')
        presql = "SELECT Sdept,Sgrade FROM student WHERE Sno = '{}'".format(id)
        print(presql)
        cur.execute(presql)
        preresult = cur.fetchall()
        print(preresult)
        sdept = preresult[0][0]
        sgrade = preresult[0][1]
        # 新增的
        presql2 = "SELECT Cou_CID FROM dbsc WHERE Stu_Sno = '{}'".format(id)
        print(presql2)
        cur.execute(presql2)
        preresult2 = cur.fetchall()
        print(preresult2)

        realneed = "("
        for i in preresult2:
            realneed = realneed + str(i[0]) + ','
        realneed = realneed[0:len(realneed) - 1] + ')'
        print(realneed)
        if realneed == ")":
            realneed = '(\'\')'
        presql3 = "SELECT Cclasstime FROM course WHERE CID in {}".format(realneed)
        print(presql3)
        cur.execute(presql3)
        preresult3 = cur.fetchall()
        print(preresult3)
        realneedtime = "("
        for i in preresult3:
            realneedtime = realneedtime + '\''+i[0] + '\','
        realneedtime = realneedtime[0:len(realneedtime) - 1] + ')'
        print(realneedtime)
        if realneedtime == ")":
            realneedtime = '(\'\')'
        sql = "SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime FROM course WHERE Cdept LIKE '%{}%' and Cgrade LIKE '%{}%' and CID not in  {} and Cclasstime not in {}".format(
            sdept, sgrade, realneed, realneedtime)
        # sql = "SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime FROM course WHERE Cdept LIKE '%{}%' and Cgrade LIKE '%{}%'".format(
        #     sdept, sgrade)
        print(sql)
        cur.execute(sql)
        result = cur.fetchall()
        result = list(result)
        num = 0
        for i in result:
            result[num] = list(result[num])
            num += 1
        print(type(result))
        print(result)

        num = 0
        for i in result:
            result[num].append(coursepeople[i[1]])
            num += 1
        print(result)
        print("课程展示结束")
        # sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID"
        # print(sql)
        # cur.execute(sql)
        # result = cur.fetchall()
        # print(result)

        sql = "SELECT Weight from dbsc where Stu_Sno = {}".format(id)
        print(sql)
        cur.execute(sql)
        result1 = cur.fetchall()
        print(result1)
        quanzhong = 0
        for i in result1:
            quanzhong += i[0]
        print(quanzhong)
        return render_template("xuankelist.html", stu_info=result, quanzhong=quanzhong)
    else:
        result = []
        quanzhong = 0
        return render_template("xuankelist.html", stu_info=result, quanzhong=quanzhong)




# 学生端选课界面课程信息查询
@app.route("/xuankelistse", methods=['POST'])
def xuankelistse():
    CID = request.form['cid']
    if len(CID) == 0:
        CID = "%"
    Cname = request.form['cname']
    if len(Cname) == 0:
        Cname = "%"
    Cclasstime = request.form['classtime']
    if len(Cclasstime) == 0:
        Cclasstime = "%"

    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    id = request.cookies.get('user')
    presql = "SELECT Sdept,Sgrade FROM student WHERE Sno = '{}'".format(id)
    print(presql)
    cur.execute(presql)
    preresult = cur.fetchall()
    print(preresult)
    sdept = preresult[0][0]
    sgrade = preresult[0][1]
    # 新增的
    presql2 = "SELECT Cou_CID FROM dbsc WHERE Stu_Sno = '{}'".format(id)
    print(presql2)
    cur.execute(presql2)
    preresult2 = cur.fetchall()
    print(preresult2)

    realneed = "("
    for i in preresult2:
        realneed = realneed + str(i[0]) + ','
    realneed = realneed[0:len(realneed) - 1] + ')'
    print(realneed)
    if realneed == ")":
        realneed = '(\'\')'

    presql3 = "SELECT Cclasstime FROM course WHERE CID in {}".format(realneed)
    print(presql3)
    cur.execute(presql3)
    preresult3 = cur.fetchall()

    realneedtime = "("
    for i in preresult3:
        realneedtime = realneedtime + '\''+i[0] + '\','
    realneedtime = realneedtime[0:len(realneedtime) - 1] + ')'
    print(realneedtime)
    if realneedtime == ")":
        realneedtime = '(\'\')'
    sql = "SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime FROM course WHERE cast(CID as char) like '{}' and Cname like '{}' and Cclasstime like '{}' and Cdept LIKE '%{}%' and Cgrade LIKE '%{}%' and CID not in {} and Cclasstime not in {}".format(
        CID, Cname, Cclasstime, sdept, sgrade, realneed,realneedtime)
    # sql = "SELECT student.Sname,student.Sno,course.Cname,Selectiontime,Weight,Whether FROM( dbsc JOIN student on dbsc.Stu_Sno = student.Sno) join course on dbsc.Cou_CID=course.CID where   student.Sname like '{}' and cast(Stu_Sno as char) like '{}' and course.Cname like '{}'".format(nam,id,course)
    # sql="SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime FROM course where cast(CID as char) like '{}' and Cname like '{}' and Cclasstime like '{}' and Cdept LIKE '%{}%' and Cgrade LIKE '%{}%'".format(CID,Cname,Cclasstime,sdept,sgrade)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    result = list(result)
    num = 0
    for i in result:
        result[num] = list(result[num])
        num += 1
    print(type(result))
    print(result)

    num = 0
    for i in result:
        result[num].append(coursepeople[i[1]])
        num += 1
    print(result)
    print("课程展示结束")

    sql = "SELECT Weight from dbsc where Stu_Sno = {}".format(id)
    print(sql)
    cur.execute(sql)
    result1 = cur.fetchall()
    print(result1)
    quanzhong = 0
    for i in result1:
        quanzhong += i[0]
    return render_template("xuankelistse.html", stu_info=result, quanzhong=quanzhong)


@app.route("/xuankeadd/<quan>/<keid>", methods=['get'])
def xuankeadd(quan, keid):
    print(quan)
    print(keid)
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sno = request.cookies.get('user')
    sql = "SELECT max(ScID) from dbsc"
    cur.execute(sql)
    scid = cur.fetchall()[0][0] + 1
    print(scid)
    sql = "INSERT into dbsc (ScID,Cou_CID,Stu_Sno,Weight)VALUES({},{},{},{})".format(scid, keid, sno, quan)

    print("这是选课")

    cur.execute(sql)
    conn.commit()
    sql = "SELECT Cname from course where CID = {}".format(keid)
    cur.execute(sql)
    cn = cur.fetchall()[0][0]
    coursepeople[cn] = coursepeople[cn]+1
    print(coursepeople)
    print(cn)
    print(sql)
    cur.execute(sql)

    return "sad"


# 学生退课按钮逻辑
@app.route("/dropcour/<cid>/<cou>", methods=['GET', 'POST'])
def dropcour(cid, cou):
    print(cid)
    print(cou)
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sno = request.cookies.get('user')
    sql = "delete  from dbsc where Cou_CID={} and Stu_Sno={}".format(cid, sno)
    print(sql)
    cur.execute(sql)
    conn.commit()
    # sql = "SELECT Cname from course where CID={}".format(cou)
    # cur.execute(sql)
    # print(sql)
    # classname = cur.fetchall()[0][0]
    # print(classname)
    coursepeople[cou] -= 1  # 减少人数放在最后
    print(coursepeople)
    print("接收到")
    return "200"

#课程表展示
@app.route("/course")
def course():
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    id = request.cookies.get('user')
    print("canselect等于",canselect)
    if canselect==0:
        presql2 = "SELECT Cou_CID FROM dbsc WHERE Stu_Sno = '{}' and Whether = 1".format(id)
    else:
        presql2 = "SELECT Cou_CID FROM dbsc WHERE Stu_Sno = '{}'".format(id)
    print(presql2)
    cur.execute(presql2)
    preresult2 = cur.fetchall()
    print(preresult2)
    #Cid = preresult2[0][0]
    realneed = "("
    for i in preresult2:
        realneed = realneed + str(i[0]) + ','
    realneed = realneed[0:len(realneed) - 1] + ')'
    print(realneed)
    if realneed== ")":
        realneed = '(\'\')'

    sql = "SELECT Cname,Cclasstime FROM course WHERE CID in {}".format(realneed)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(result)

    list1 = []
    list2 = [list(list1) for i in range(0, 56)]
    print(list2)

    # result = [['概率论', '3,5'], ['数据结构', '2,1']]
    for i in result:
        m = i[1].split(',')
        print(m)
        a = int(m[0])
        b = int(m[1])
        print(a)
        print(b)
        n = 7 * b + a - 8
        list2[n].append(i[0])
    print(list2)
    n = 7
    list3 = [list2[i:i + n] for i in range(0, len(list2), n)]
    no1 = list3[0]
    no2 = list3[1]
    no3 = list3[2]
    no4 = list3[3]
    no5 = list3[4]
    no6 = list3[5]
    no7 = list3[6]
    no8 = list3[7]
    print(no1)
    print(no2)
    print(no3)
    print(no4)
    print(no5)
    print(no6)
    print(no7)
    print(no8)

    return render_template("percour1.html", no1=no1, no2=no2,no3=no3,no4=no4, no5=no5,no6=no6,no7=no7, no8=no8)




#2021-11-4 21：17 cjc
#已选课程查看
@app.route("/yixuanlist")
def yixuanlist():
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    id = request.cookies.get('user')
    presql="SELECT Sdept,Sgrade FROM student WHERE Sno = '{}'".format(id)
    print(presql)
    cur.execute(presql)
    preresult = cur.fetchall()
    print(preresult)
    sdept=preresult[0][0]
    sgrade=preresult[0][1]


    if canselect==1:
        presql2 = "SELECT Cou_CID FROM dbsc WHERE Stu_Sno = '{}'".format(id)

    else:
        presql2 = "SELECT Cou_CID FROM dbsc WHERE Stu_Sno = '{}' and Whether = 1".format(id)
    print(presql2)
    cur.execute(presql2)
    preresult2 = cur.fetchall()
    print(preresult2)
    realcalss = "("
    for i in preresult2:
        realcalss = realcalss + str(i[0]) + ','
    realcalss = realcalss[0:len(realcalss) - 1] + ')'

    Cid = realcalss
    if Cid== ")":
        Cid = '(\'\')'
    sql = "SELECT CID,Cname,Climitnum,Ccredit,Cstartdate,Cenddate,Clocation,Cteacher,Cclasstime FROM course WHERE Cdept LIKE '%{}%' and Cgrade LIKE '%{}%' and CID in {}".format(sdept,sgrade,Cid)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    result = list(result)
    num = 0
    for i in result:
        result[num] = list(result[num])
        num += 1
    print(type(result))
    print(result)

    num = 0
    print(coursepeople)
    allquan = 0
    for i in result:
        result[num].append(coursepeople[i[1]])
        sql = "SELECT Weight from dbsc where Stu_Sno = {} and Cou_CID = {}".format(id,result[num][0])
        print(sql)
        cur.execute(sql)
        quanzhong = cur.fetchall()[0][0]
        allquan+=quanzhong
        result[num].append(quanzhong)
        num += 1
    print(result)
    print("已选课程展示结束")
    if canselect==1:
        return render_template("yixuanlist.html",stu_info=result,quanzhong=allquan)
    else:
        return render_template("yixuanlist2.html", stu_info=result, quanzhong=allquan)

#修改权重
@app.route("/modifyweight/<quan>/<keid>", methods=['get'])
def modifyweight(quan, keid):
    print(quan)
    print(keid)
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sno = request.cookies.get('user')
    # sql = "SELECT max(ScID) from dbsc"
    # cur.execute(sql)
    # scid = cur.fetchall()[0][0] + 1
    # print(scid)
    # sql = "INSERT into dbsc (ScID,Cou_CID,Stu_Sno,Weight)VALUES({},{},{},{})".format(scid, keid, sno, quan)
    sql = "UPDATE dbsc SET Weight = '{}' WHERE Cou_CID = '{}' and Stu_Sno = '{}'".format(quan,keid,sno)
    print(sql)
    cur.execute(sql)
    conn.commit()
    return "修改成功"


@app.route("/jieshu")
def jieshu():
    global canselect
    canselect=0
    print("canselect等于",canselect)
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    for i,j in coursepeople.items():
        sql = "SELECT CID,Climitnum FROM course where Cname = '{}'".format(i)
        cur.execute(sql)
        print(sql)
        result = cur.fetchall()
        cid = result[0][0]
        maxpeo = result[0][1]
        getid(cid)
    return "asd"

'''
    对每个课程调用此函数
'''


def getid(course):  # 传入的course为课程的id
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sql = "SELECT Climitnum FROM course where CID={}".format(course)
    cur.execute(sql)
    maxpeople = cur.fetchall()[0][0]
    print(maxpeople)
    sql = "SELECT Stu_Sno FROM dbsc where Cou_CID = {} ORDER BY Weight DESC LIMIT 0,{};".format(course, maxpeople)
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    print(result)
    # result[i][0]指选上课的学生
    m = "("
    for i in result:
        m = m + str(i[0]) + ','
    m = m[0:len(m) - 1] + ')'
    print(m)
    if m!=')':
        sql = "UPDATE dbsc set Whether = 1 where Stu_Sno in {} and Cou_CID={}".format(m, course)
        cur.execute(sql)
        conn.commit()




if __name__ == '__main__':
    conn = pymysql.connect(host=host, user="root", password="123456", db="mysql", port=3306,
                           charset="utf8")
    cur = conn.cursor()
    sql = "SELECT Cname FROM course"
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    for i in result:
        coursepeople[i[0]] = 0

    sql = "SELECT  Cname,suo from course inner join (SELECT Cou_CID,COUNT(*) suo from dbsc GROUP BY Cou_CID) b on course.CID=b.Cou_CID"
    cur.execute(sql)
    result = cur.fetchall()
    for i in result:
        coursepeople[i[0]] += i[1]
    sql = "update dbsc set Whether=0"
    cur.execute(sql)
    conn.commit()
    print(coursepeople)
    app.run(port=8080, debug=True)
